﻿using LibraryManagementSystem.Models;
using LibraryManagementSystem.Services.Interfaces;
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Threading.Tasks;

public class BookService : IBookService
{
    private readonly LibraryDbContext _dbContext;

    public BookService(LibraryDbContext dbContext)
    {
        _dbContext = dbContext;
    }

    public async Task<List<Book>> SearchBooks(string keyword)
    {
        var books = new List<Book>();

        using (var command = _dbContext.CreateCommand())
        {
            command.CommandText = @"
                SELECT * FROM Books 
                WHERE Title LIKE @Keyword OR Author LIKE @Keyword OR ISBN LIKE @Keyword";
            command.Parameters.AddWithValue("@Keyword", $"%{keyword}%");

            using (var reader = await command.ExecuteReaderAsync())
            {
                // 打印出列信息
                for (int i = 0; i < reader.FieldCount; i++)
                {
                    Console.WriteLine($"Column {i}: Name = {reader.GetName(i)}, Type = {reader.GetDataTypeName(i)}");
                }

                while (await reader.ReadAsync())
                {
                    var book = new Book
                    {
                        ISBN = reader.GetString(reader.GetOrdinal("ISBN")),
                        Title = reader.GetString(reader.GetOrdinal("Title")),
                        Author = reader.GetString(reader.GetOrdinal("Author")),
                        Publisher = reader.GetString(reader.GetOrdinal("Publisher")),
                        TotalCopies = reader.GetInt32(reader.GetOrdinal("TotalCopies")),
                        AvailableCopies = reader.GetInt32(reader.GetOrdinal("AvailableCopies")),
                        Location = reader.GetString(reader.GetOrdinal("Location")),
                        Type = reader.GetString(reader.GetOrdinal("Type"))
                    };
                    books.Add(book);
                }
            }
        }

        return books;
    }

    public async Task<bool> AddBook(Book book)
    {
        using (var command = _dbContext.CreateCommand())
        {
            command.CommandText = @"
                INSERT INTO Books (ISBN, Title, Author, Publisher, TotalCopies, AvailableCopies, Location, Type)
                VALUES (@ISBN, @Title, @Author, @Publisher, @TotalCopies, @AvailableCopies, @Location, @Type)";

            command.Parameters.AddWithValue("@ISBN", book.ISBN);
            command.Parameters.AddWithValue("@Title", book.Title);
            command.Parameters.AddWithValue("@Author", book.Author);
            command.Parameters.AddWithValue("@Publisher", book.Publisher);
            command.Parameters.AddWithValue("@TotalCopies", book.TotalCopies);
            command.Parameters.AddWithValue("@AvailableCopies", book.AvailableCopies);
            command.Parameters.AddWithValue("@Location", book.Location);
            command.Parameters.AddWithValue("@Type", book.Type);

            var rowsAffected = await command.ExecuteNonQueryAsync();
            return rowsAffected > 0;
        }
    }

    public async Task<bool> UpdateBook(Book book)
    {
        using (var command = _dbContext.CreateCommand())
        {
            command.CommandText = @"
                UPDATE Books 
                SET Title = @Title, Author = @Author, Publisher = @Publisher, 
                    TotalCopies = @TotalCopies, AvailableCopies = @AvailableCopies, 
                    Location = @Location, Type = @Type
                WHERE ISBN = @ISBN";

            command.Parameters.AddWithValue("@ISBN", book.ISBN);
            command.Parameters.AddWithValue("@Title", book.Title);
            command.Parameters.AddWithValue("@Author", book.Author);
            command.Parameters.AddWithValue("@Publisher", book.Publisher);
            command.Parameters.AddWithValue("@TotalCopies", book.TotalCopies);
            command.Parameters.AddWithValue("@AvailableCopies", book.AvailableCopies);
            command.Parameters.AddWithValue("@Location", book.Location);
            command.Parameters.AddWithValue("@Type", book.Type);

            var rowsAffected = await command.ExecuteNonQueryAsync();
            return rowsAffected > 0;
        }
    }

    public async Task<bool> DeleteBook(string ISBN)
    {
        using (var command = _dbContext.CreateCommand())
        {
            command.CommandText = "DELETE FROM Books WHERE ISBN = @ISBN";
            command.Parameters.AddWithValue("@ISBN", ISBN);

            var rowsAffected = await command.ExecuteNonQueryAsync();
            return rowsAffected > 0;
        }
    }

    public async Task<bool> BorrowBook(int username, string ISBN)
    {
        using (var transaction = await _dbContext.BeginTransactionAsync())
        {
            try
            {
                // Check if the book is available
                using (var command = _dbContext.CreateCommand())
                {
                    command.Transaction = transaction;
                    command.CommandText = "SELECT AvailableCopies FROM Books WHERE ISBN = @ISBN";
                    command.Parameters.AddWithValue("@ISBN", ISBN);

                    var availableCopies = Convert.ToInt32(await command.ExecuteScalarAsync());
                    if (availableCopies <= 0)
                    {
                        return false;
                    }
                }

                // Decrease available copies
                using (var command = _dbContext.CreateCommand())
                {
                    command.Transaction = transaction;
                    command.CommandText = "UPDATE Books SET AvailableCopies = AvailableCopies - 1 WHERE ISBN = @ISBN";
                    command.Parameters.AddWithValue("@ISBN", ISBN);
                    await command.ExecuteNonQueryAsync();
                }

                // Add borrow record
                using (var command = _dbContext.CreateCommand())
                {
                    command.Transaction = transaction;
                    command.CommandText = @"
                        INSERT INTO BorrowRecords (Username, ISBN, BorrowDate, DueDate, Status)
                        VALUES (@Username, @ISBN, @BorrowDate, @DueDate, @Status)";

                    command.Parameters.AddWithValue("@Username", username);
                    command.Parameters.AddWithValue("@ISBN", ISBN);
                    command.Parameters.AddWithValue("@BorrowDate", DateTime.Now);
                    command.Parameters.AddWithValue("@DueDate", DateTime.Now.AddDays(30));
                    command.Parameters.AddWithValue("@Status", "borrowed");

                    await command.ExecuteNonQueryAsync();
                }

                transaction.Commit();
                return true;
            }
            catch
            {
                transaction.Rollback();
                throw;
            }
        }
    }

    public async Task<bool> ReturnBook(string ISBN)
    {
        using (var transaction = await _dbContext.BeginTransactionAsync())
        {
            try
            {
                // Increase available copies
                using (var command = _dbContext.CreateCommand())
                {
                    command.Transaction = transaction;
                    command.CommandText = "UPDATE Books SET AvailableCopies = AvailableCopies + 1 WHERE ISBN = @ISBN";
                    command.Parameters.AddWithValue("@ISBN", ISBN);
                    await command.ExecuteNonQueryAsync();
                }

                // Update borrow record status
                using (var command = _dbContext.CreateCommand())
                {
                    command.Transaction = transaction;
                    command.CommandText = @"
                        UPDATE BorrowRecords 
                        SET Status = 'returned', ReturnDate = @ReturnDate 
                        WHERE ISBN = @ISBN AND Status = 'borrowed'";

                    command.Parameters.AddWithValue("@ISBN", ISBN);
                    command.Parameters.AddWithValue("@ReturnDate", DateTime.Now);

                    var rowsAffected = await command.ExecuteNonQueryAsync();
                    if (rowsAffected == 0)
                    {
                        return false;
                    }
                }

                transaction.Commit();
                return true;
            }
            catch
            {
                transaction.Rollback();
                throw;
            }
        }
    }

    public async Task<List<BorrowRecord>> GetUserBorrowRecords(int username)
    {
        var borrowRecords = new List<BorrowRecord>();

        using (var command = _dbContext.CreateCommand())
        {
            command.CommandText = @"
                SELECT * FROM BorrowRecords 
                WHERE Username = @Username";
            command.Parameters.AddWithValue("@Username", username);

            using (var reader = await command.ExecuteReaderAsync())
            {
                // 打印出列信息
                for (int i = 0; i < reader.FieldCount; i++)
                {
                    Console.WriteLine($"Column {i}: Name = {reader.GetName(i)}, Type = {reader.GetDataTypeName(i)}");
                }

                while (await reader.ReadAsync())
                {
                    var record = new BorrowRecord
                    {
                        Username = reader.GetInt32(reader.GetOrdinal("Username")), 
                        ISBN = reader.GetString(reader.GetOrdinal("ISBN")),
                        BorrowDate = reader.GetDateTime(reader.GetOrdinal("BorrowDate")),
                        DueDate = reader.GetDateTime(reader.GetOrdinal("DueDate")),
                        ReturnDate = reader.IsDBNull(reader.GetOrdinal("ReturnDate"))
                            ? (DateTime?)null
                            : reader.GetDateTime(reader.GetOrdinal("ReturnDate")),
                        Status = reader.GetString(reader.GetOrdinal("Status"))
                    };
                    borrowRecords.Add(record);
                }
            }
        }

        return borrowRecords;
    }
}